package com.tanhua.manage.mapper;

import com.tanhua.manage.enums.SexEnum;
import com.tanhua.manage.pojo.Retention;
import com.tanhua.manage.vo.UserTimeVo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
@Mapper
public interface RetentionMapper {

    /**
     * 查询出所有满足条件的user的id和created时间
     */
    @Select("select id, created  from tb_user where created between #{minDate} and #{maxDate}")
    List<UserTimeVo> queryRegister(@Param("minDate") Date minDate, @Param("maxDate") Date maxDate);

    /**
     * 根据useId查询用户登录时间
     */
    @Select("select " +
            "log_time from tb_log where user_id = ${userId} and type = '01'")
    List<Date> queryLogTime(@Param("userId") Long userId);

    /**
     * 查询最早用户注册时间  2019-08-06
     */
    @Select("select created from tb_user order by created ASC limit 1")
    Date queryMinRegisterTime();

    /**
     * 查询最晚用户注册时间  2020-09-25
     */
    @Select("select created from tb_user order by created DESC limit 1")
    Date queryMaxRegisterTime();


    /**
     * 存入retention信息
     */
    @Insert("insert into tb_retention values(#{id},#{userId},#{registerTime},#{logTime},#{days},#{sex})")
    void insertRetention(Retention retention);

    /**
     * 根据时间间隔和日期查询登录人数
     */
    @Select("select count(*) from tb_retention where register_time = #{register} and days >= #{days}")
    Integer queryRetention(@Param("register") String register,
                           @Param("days") Integer days);

    /**
     * 根据时间间隔、日期和性别查询登录人数
     */
    @Select("select count(*) from tb_retention where register_time = #{register} and days >= #{days} and sex = #{sex}")
    Integer queryRetentionWithSex(@Param("register") String register,
                           @Param("days") Integer days,
                           @Param("sex") Integer sex);

    /**
     * 查询当天注册人数
     */
    @Select("select num_registered from tb_analysis_by_day where record_date = #{register}")
    Integer queryNumRegister(@Param("register") Date register);

    /**
     * 查询用户性别
     */
    @Select("SELECT sex from tb_user_info WHERE user_id = #{userId}")
    Integer querySex(@Param("userId") Long userId);

}
